<?php

class Db_Joins_ItemJoins
{
	var $level_id;
	var $page_id;
	var $page_size = 30;
	var $skip_pagination;
	var $keyword;
	var $channel_id;
	
	function __construct() {
		$this->db = Zend_Registry::get("db");
	}
	
	function GetLevel1ItemList()
	{
		$result = array();
		
		//count total for pagination
		$data = $this->db->select();
		$data->from("level1", array("count(id) as ct"));
		$ct = $this->db->fetchRow($data);

		//dump all in condition
		$data = $this->db->select();
		$data->from("level1 as l", array("id", "title", "joined_members", "ctime", "status", "memo1"));
		$data->joinLeft("channels as c", "c.channel_id=l.channel_id", "channel_name");
		if($this->keyword)
		{
			$data->where("l.title like '%".$this->keyword."%'");
		}
		if($this->channel_id)
		{
			$data->where("l.channel_id = ?", $this->channel_id);
		}
		if($this->page_id && !$this->skip_pagination)
		{
			$data->limit($this->page_size, $this->page_size * ($this->page_id - 1));
		}
		$data->order("l.ctime DESC");
		$rows = $this->db->fetchAll($data);

		$result = array(
			'total_pages' => ceil($ct['ct'] / $this->page_size),
			'current_page_info' => $rows
		);
		
		return $result;
	}
	
	function GetLevel1Info()
	{
		$result = array();
		
		if($this->level_id)
		{
			$data = $this->db->select();
			$data->from("level1", "*");
			$data->where("id = ?", $this->level_id);
			$result = $this->db->fetchRow($data);
		}
		
		return $result;
	}
	
	function GetLevel2ItemList()
	{
		$result = array();
		
		//count total for pagination
		$data = $this->db->select();
		$data->from("level2", array("count(id) as ct"));
		$data->where("level1_id = ?", $this->level_id);
		$ct = $this->db->fetchRow($data);

		//dump all in condition
		$data = $this->db->select();
		$data->from("level2", array("id", "image_url", "joined_members", "ctime", "status"));
		$data->where("level1_id = ?", $this->level_id);
		
		if($this->page_id && !$this->skip_pagination)
		{
			$data->limit($this->page_size, $this->page_size * ($this->page_id - 1));
		}
		
		$data->order("id DESC");
		$rows = $this->db->fetchAll($data);

		$result = array(
			'total_pages' => ceil($ct['ct'] / $this->page_size),
			'current_page_info' => $rows
		);
		
		return $result;
	}
	
	function GetLevel2Info()
	{
		$result = array();
		
		if($this->level_id)
		{
			$data = $this->db->select();
			$data->from("level2", "*");
			$data->where("id = ?", $this->level_id);
			$result = $this->db->fetchRow($data);
		}
		
		return $result;
	}
	
	function GetLevel3ItemList()
	{
		$result = array();
		
		//count total for pagination
		$data = $this->db->select();
		$data->from("level3", array("count(id) as ct"));
		$data->where("level2_id = ?", $this->level_id);
		$ct = $this->db->fetchRow($data);

		//dump all in condition
		$data = $this->db->select();
		$data->from("level3", array("id", "banner_text", "joined_members", "ctime", "status"));
		$data->where("level2_id = ?", $this->level_id);
		
		if($this->page_id && !$this->skip_pagination)
		{
			$data->limit($this->page_size, $this->page_size * ($this->page_id - 1));
		}
		$data->order("id DESC");
		$rows = $this->db->fetchAll($data);

		$result = array(
			'total_pages' => ceil($ct['ct'] / $this->page_size),
			'current_page_info' => $rows
		);
		
		return $result;
	}
	
	function GetLevel3Info()
	{
		$result = array();
		
		if($this->level_id)
		{
			$data = $this->db->select();
			$data->from("level3", "*");
			$data->where("id = ?", $this->level_id);
			$result = $this->db->fetchRow($data);
		}
		
		return $result;
	}
} 